package com.situ.achievementsystem.project.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.achievementsystem.project.model.ProjectModel;
import com.situ.achievementsystem.util.JDBCUtil;

public class ProjectDao {

	private Connection con = null;
	private PreparedStatement ps = null;
	
	public String insert(ProjectModel model) {
		String sql = "insert into project(code,name)values(?,?)";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getCode());
			ps.setString(2,model.getName());
			int res = ps.executeUpdate();
			return res + "";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
	
	public String delete(ProjectModel model) {
		String sql = "delete from project where code = ?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getCode());
			int res = ps.executeUpdate();
			return res+"";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
	
	public String update(ProjectModel model) {
		String sql = "update project set name =? where code=?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getName());
			ps.setString(2,model.getCode());
			int res = ps.executeUpdate();
			return res + "";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
	
	public List<ProjectModel> select(ProjectModel model){
		StringBuilder sql = new StringBuilder("select code,name from project where 1=1 ");
		List<Object> list = appendWhere(sql,model);
		ResultSet rs = null;
		List<ProjectModel> result = new ArrayList<>();
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql.toString());
			for(int i=0;i<list.size();i++) {
				ps.setObject(i+1,list.get(i));
			}
			rs = ps.executeQuery();
			while(rs.next()) {
				String code = rs.getString("code");
				String name = rs.getString("name");
				result.add(new ProjectModel(code,name));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	private List<Object> appendWhere(StringBuilder sql, ProjectModel model) {
		List<Object> list = new ArrayList<>(); 
		String code = model.getCode();
		if(code!=null && !code.trim().isEmpty()) {
			sql.append(" and code like ? ");
			list.add(code);
		}
		String name = model.getName();
		if(name!=null&&!name.trim().isEmpty()) {
			sql.append(" and name like ? ");
			list.add(name);
		}
		if (model.isPageOn()) {
            sql.append(" limit ?,? ");
            list.add(model.getRowStart());
            list.add(model.getPageLimit());
        }
		return list;
	}
	
	public Integer selectCount(ProjectModel model) {
        StringBuilder sql = new StringBuilder("select count(1) from project where 1=1 ");
        List<Object> vals = appendWhere(sql, model);
        return queryInt(sql.toString(), vals);
	}
	
	public static Integer queryInt(String sql, List<Object> vals) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
        	con = JDBCUtil.getConnection();
            ps = con.prepareStatement(sql.toString());
            for (int i = 0; i < vals.size(); i++)
                ps.setObject(i + 1, vals.get(i));
            rs = ps.executeQuery();
            if (rs.next())
                return rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(con, ps, rs);
        }
        return null;
    }
}

